﻿using System.Data;
using System.Data.OleDb;

namespace VSTODemo {
    public class EmployeeService {
        private static OleDbConnection conn = null;

        public static OleDbConnection GetConnection() {
            string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\my-documents\Sample Database\Employees.accdb";
            var conn = new OleDbConnection(connStr);
           
            return conn;
        }

        public static DataTable ListAll() {
            var conn = EmployeeService.GetConnection();
            string selectCmd = "select * from employees";
            OleDbDataAdapter adapter = new OleDbDataAdapter(selectCmd, conn);

            DataTable employees = new DataTable();
            adapter.Fill(employees);
            return employees;
        }

        public static DataTable GetDataFromSql(string sqlStatement) {
            var conn = EmployeeService.GetConnection();
            string selectCmd = sqlStatement;
            OleDbDataAdapter adapter = new OleDbDataAdapter(selectCmd, conn);

            DataTable values = new DataTable();
            adapter.Fill(values);
            return values;
        }
    }    
}
